<?php
	$search_volunteer_sql = '
		select *
		from (volunteer_information Voinf left join sub_category sCate on Voinf.sub_category_id = sCate.sub_category_id)
			left join category mCate
				on mCate.main_category_id = sCate.main_category_id
		where (Voinf.volunteer_name like ?
		or Voinf.content like ?
		or Voinf.target_gender like ?
		or Voinf.location like ?
		or Voinf.access like ?
		or Voinf.pref like ?
		or sCate.sub_category_name like ?
		or mCate.main_category_name like ?)
		';

	$search_volunteer_count_sql = '
		select count(*)
		from (volunteer_information Voinf left join sub_category sCate on Voinf.sub_category_id = sCate.sub_category_id)
			left join category mCate
				on mCate.main_category_id = sCate.main_category_id
		where (Voinf.volunteer_name like ?
		or Voinf.content like ?
		or Voinf.target_gender like ?
		or Voinf.location like ?
		or Voinf.access like ?
		or Voinf.pref like ?
		or sCate.sub_category_name like ?
		or mCate.main_category_name like ?)
		';

	$volunteer_and_sql = '
		 and (Voinf.volunteer_name like ?
		or Voinf.content like ?
		or Voinf.target_gender like ?
		or Voinf.location like ?
		or Voinf.access like ?
		or Voinf.pref like ?
		or sCate.sub_category_name like ?
		or mCate.main_category_name like ?)
		';
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	$search_thread_sql = '
		select *
		from thread
		where thread_name like ?
		';

	$search_thread_count_sql = '
		select count(*)
		from thread
		where thread_name like ?
		';

	$thread_and_sql = ' and thread_name like ?';
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	$category_sql = '
		select *
		from volunteer_information Voinf left join sub_category sCate on Voinf.sub_category_id = sCate.sub_category_id
		where sCate.sub_category_name = ?
		';

	$category_count_sql = '
		select count(*)
		from volunteer_information Voinf left join sub_category sCate on Voinf.sub_category_id = sCate.sub_category_id
		where sCate.sub_category_name = ?
		';
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	$pref_sql = '
		select *
		from volunteer_information
		where pref = ?
	';

	$pref_count_sql = '
		select count(*)
		from volunteer_information
		where pref = ?
	';
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	$refine_sql = '
		select *
		from (volunteer_information Voinf left join sub_category sCate on Voinf.sub_category_id = sCate.sub_category_id)
			left join category mCate
				on mCate.main_category_id = sCate.main_category_id
		where
	';
	$refine_count_sql = '
		select count(*)
		from (volunteer_information Voinf left join sub_category sCate on Voinf.sub_category_id = sCate.sub_category_id)
			left join category mCate
				on mCate.main_category_id = sCate.main_category_id
		where
	';
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

//Refine_word　絞込ワード
	$flag = false;
	$refine_word = array();
	if (!empty($_GET['refine_word_sub_category']))
	{
		array_push($refine_word,htmlspecialchars($_GET['refine_word_sub_category']));
		if ($flag)
		{
			$refine_sql = $refine_sql." and sCate.sub_category_name like ?";
			$refine_count_sql = $refine_count_sql." and sCate.sub_category_name like ?";
		}
		else
		{
			$refine_sql = $refine_sql." sCate.sub_category_name like ?";
			$refine_count_sql = $refine_count_sql." sCate.sub_category_name like ?";
			$flag = true;
		}
	}

	if (!empty($_GET['refine_word_pref']))
	{
		array_push($refine_word,htmlspecialchars($_GET['refine_word_pref']));
		if ($flag)
		{
			$refine_sql = $refine_sql." and Voinf.pref like ?";
			$refine_count_sql = $refine_count_sql." and Voinf.pref like ?";
		}
		else
		{
			$refine_sql = $refine_sql." Voinf.pref like ?";
			$refine_count_sql = $refine_count_sql." Voinf.pref like ?";
			$flag = true;
		}
	}

	if (!empty($_GET['refine_word_gender']))
	{
		array_push($refine_word,htmlspecialchars($_GET['refine_word_gender']));
		if ($flag)
		{
			$refine_sql = $refine_sql." and Voinf.target_gender like ?";
			$refine_count_sql = $refine_count_sql." and Voinf.target_gender like ?";
		}
		else
		{
			$refine_sql = $refine_sql." Voinf.target_gender like ?";
			$refine_count_sql = $refine_count_sql." Voinf.target_gender like ?";
			$flag = true;
		}
	}

	if (!empty($_GET['refine_free_word']))
	{
		for ($i = 0;$i <= 7;$i++)
		{
			echo $i;
			array_push($refine_word,htmlspecialchars($_GET['refine_free_word']));
		}
		if ($flag)
		{
			$refine_sql = $refine_sql." and (Voinf.volunteer_name like ?
										or Voinf.content like ?
										or Voinf.target_gender like ?
										or Voinf.location like ?
										or Voinf.access like ?
										or Voinf.pref like ?
										or sCate.sub_category_name like ?
										or mCate.main_category_name like ?)";

			$refine_count_sql = $refine_count_sql." and (Voinf.volunteer_name like ?
										or Voinf.content like ?
										or Voinf.target_gender like ?
										or Voinf.location like ?
										or Voinf.access like ?
										or Voinf.pref like ?
										or sCate.sub_category_name like ?
										or mCate.main_category_name like ?)";
		}
		else
		{
			$refine_sql = $refine_sql." (Voinf.volunteer_name like ?
										or Voinf.content like ?
										or Voinf.target_gender like ?
										or Voinf.location like ?
										or Voinf.access like ?
										or Voinf.pref like ?
										or sCate.sub_category_name like ?
										or mCate.main_category_name like ?)";

			$refine_count_sql = $refine_count_sql." (Voinf.volunteer_name like ?
										or Voinf.content like ?
										or Voinf.target_gender like ?
										or Voinf.location like ?
										or Voinf.access like ?
										or Voinf.pref like ?
										or sCate.sub_category_name like ?
										or mCate.main_category_name like ?)";
			$flag = true;
		}
	}
	if ((!empty($_GET['refine_word_sub_category'])) || (!empty($_GET['refine_word_pref'])) || (!empty($_GET['refine_word_gender'])) || (!empty($_GET['refine_free_word']))) {
		$sth = $db->prepare($refine_count_sql);
		$sth = $db->execute($sth,$refine_word);
		echo "<center><a>検索の結果".array_shift($sth->fetchRow(DB_FETCHMODE_ORDERED))."件見つかりました</a></center>";

		$sth = $db->prepare($refine_sql);
		$sth = $db->execute($sth,$refine_word);
		while ($row =& $sth->fetchRow(DB_FETCHMODE_ASSOC))
		{
			echo "<table id='table-02'><tr><th width='120px'>ボランティア名</th><a href='../volunteer_function/display_volunteer_information.php?volunteer_information_id=".$row['volunteer_information_id']."'><td valign='middle'>".$row['volunteer_name']."</td></a></tr><tr><th>内容</th><td>".$row['content']."</td></tr></tabel>";
		}
	}


	$get_word 				= "";			//フリーワード検索の検索ワードを格納する変数
	$search_words 			= array();			//複数キーワード検索に対応するための変数
	$targetpage 			= "volunteer";	//検索を行う範囲のターゲットを指定するための変数
	$count_array 			= 0;			//検索ワードの個数を格納する変数

//フリーワード検索準備開始
	if (!empty($_POST['search_word']))
	{
		$get_word  = htmlspecialchars($_POST['search_word'],ENT_QUOTES,"UTF-8");
		$search_words = explode("　",$get_word);
		$count_array  = count($search_words)-1;
//フリーワード検索　ターゲット指定（ボランティア：掲示板）sql文の生成
		if (!empty($_GET['target']))
		{
			$targetpage = htmlspecialchars($_GET['target'],ENT_QUOTES,"UTF-8");
			switch ($targetpage)
			{
				case "volunteer":
					if ($count_array > 0) {
						for ($i = 0; $i < $count_array; $i++)
						{
							$search_volunteer_sql = $search_volunteer_sql.$volunteer_and_sql;
							$search_volunteer_count_sql = $search_volunteer_count_sql.$volunteer_and_sql;
						}
					}
				break;

				case "thread":
					if ($count_array > 0) {
						for ($i = 0; $i < $count_array; $i++)
						{
							$search_thread_sql = $search_thread_sql.$thread_and_sql;
							$search_thread_count_sql = $search_thread_count_sql.$thread_and_sql;
						}
					}
				break;
			}
		}
		else
		{
			if ($count_array > 0)
			{
				for ($i = 0; $i < $count_array; $i++)
				{
					$search_volunteer_sql = $search_volunteer_sql.$volunteer_and_sql;
					$search_volunteer_count_sql = $search_volunteer_count_sql.$volunteer_and_sql;
				}
			}
		}
//フリーワード検索　検索対象文字列を配列で生成（ボランティア）
		$target_volunteer_array = array();//検索対象の文字列を格納する配列

		$volunteer_word_count = 0;//for文で使うカウント用の変数
		for ($i = 0; $i <= $count_array; $i++)
		{
			for ($y = 0; $y <= 7; $y++)
			{
				$target_volunteer_array[$volunteer_word_count] = "%".$search_words[$i]."%";
				$volunteer_word_count += 1;
			}
		}
//フリーワード検索　検索対象文字列を配列で生成（掲示板）
		$target_thread_array = array();//検索対象の文字列を格納する配列

		$thread_word_count = 0;//for文で使うカウント用の変数
		for ($i = 0; $i <= $count_array; $i++)
		{
			$target_thread_array[$thread_word_count] = "%".$search_words[$i]."%";
		}
//フリーワード検索　sql文の実行・結果表示
		if(!empty($get_word))
		{
			switch ($targetpage)
			{
				case "volunteer":
					$sth = $db->prepare($search_volunteer_count_sql);
					$sth = $db->execute($sth,$target_volunteer_array);
					echo "<center><a>検索の結果".array_shift($sth->fetchRow(DB_FETCHMODE_ORDERED))."件見つかりました</a></center>";

					$sth = $db->prepare($search_volunteer_sql);
					$sth = $db->execute($sth,$target_volunteer_array);

					while ($row =& $sth->fetchRow(DB_FETCHMODE_ASSOC))
					{
						echo "<table id='table-02'><tr><th width='120px'>ボランティア名</th><td>".$row['volunteer_name']."</td></tr><tr><th>内容</th><td>".$row['content']."</td></tr></tabel>";
					}
					break;
				case "thread":
					$sth = $db->prepare($search_thread_count_sql);
					$sth = $db->execute($sth,$target_thread_array);
					echo "<center><a>検索の結果".array_shift($sth->fetchRow(DB_FETCHMODE_ORDERED))."件見つかりました</a></center>";

					$sth = $db->prepare($search_thread_sql);
					$sth = $db->execute($sth,$target_thread_array);

					while ($row = $sth->fetchRow(DB_FETCHMODE_ASSOC))
					{
						echo "<table id='table-02'><tr><th width='120px'>スレッド名</th><td>".$row['thread_name']."</td></tr><tr><th>最新</th><td>".$row['thread_date']."</tr></tabel>";
					}
					break;
				default:
					$sth = $db->prepare($search_volunteer_count_sql);
					$sth = $db->execute($sth,$target_volunteer_array);
					echo "<center><a>検索の結果".array_shift($sth->fetchRow(DB_FETCHMODE_ORDERED))."件見つかりました</a></center>";

					$sth = $db->prepare($search_volunteer_sql);
					$sth = $db->execute($sth,$target_volunteer_array);

					while ($row =& $sth->fetchRow(DB_FETCHMODE_ASSOC))
					{
						echo "<table id='table-02'><tr><th width='120px'>ボランティア名</th><td>".$row['volunteer_name']."</td></tr><tr><th>内容</th><td>".$row['content']."</td></tr></tabel>";
					}
					break;
			}
		}
	}
	$db->disconnect();